

'''
-- 用户操作日志表    
CREATE TABLE user_operation_log (    
    log_id SERIAL PRIMARY KEY,    
    user_id INTEGER NOT NULL REFERENCES user_info(user_id),    
    operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,    
    operation_type VARCHAR(20) NOT NULL,    
    operation_content VARCHAR(200) NOT NULL    
);
'''
import logging

logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)

class UserOperationLogTableMethod:

    def __init__(self, conn, cursor):
        self.cursor = cursor
        self.conn = conn

    def insert_user_operation_log(self, user_id, operation_type, operation_content):

        try:
            sql = "INSERT INTO user_operation_log (user_id, operation_type, operation_content) VALUES (%s, %s, %s)"
            params = (user_id, operation_type, operation_content)
            self.cursor.execute(sql, params)
            self.conn.commit()
            return True
        except Exception as e:
            print(e)
            return False

    def get_user_operation_log_by_user_id(self, user_id, limit=10, page=1):

        sql = "SELECT * FROM user_operation_log WHERE user_id = %s ORDER BY operation_time DESC LIMIT %s OFFSET %s"
        params = (user_id, limit, (page-1)*limit)
        self.cursor.execute(sql, params)
        result = self.cursor.fetchall()
        
        return result

        
    
    def get_user_operation_log_count_by_user_id(self, user_id):

        try:
            sql = "SELECT COUNT(*) FROM user_operation_log WHERE user_id = %s"
            params = (user_id,)
            self.cursor.execute(sql, params)
            result = self.cursor.fetchone()
            return result["COUNT(*)"]
        
        except Exception as e:
            print(e)
            return None
        
    def get_user_operation_log_by_user_id_and_operation_type(self, user_id, operation_type, limit=10, page=1):

        try:
            sql = "SELECT * FROM user_operation_log WHERE user_id = %s AND operation_type = %s ORDER BY operation_time DESC LIMIT %s OFFSET %s"
            params = (user_id, operation_type, limit, (page-1)*limit)            
            self.cursor.execute(sql, params)
            result = self.cursor.fetchall()
            return result            
        except Exception as e:

            return None
        
    def get_all_user_operation_log(self, limit=10, page=1):

        sql = "SELECT * FROM user_operation_log ORDER BY operation_time DESC LIMIT %s OFFSET %s"
        params = (limit, (page-1)*limit)
        self.cursor.execute(sql, params)
        result = self.cursor.fetchall()
        return result
        
    